﻿<%@ WebHandler Language="C#" Class="SIPO.Web.AjaxHandler.Transaction.recRegistrationhandler" %>

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using SIPO.Library.DataAccess.Transaction;
using SIPO.Library.DomainObject.Transaction;
using SIPO.Library.DataAccess.Administration;
using SIPO.Library.DataAccess.Administration.Catalog;
using SIPO.Library.DataAccess.Administration.UserManagement;
using SIPO.Library.DomainObject.Administration;
using SIPO.Library.DomainObject.Administration.UserManagement;
using SIPO.Library.DomainObject.Administration.Catalog;
using SIPO.Library.Enum;
using SIPO.Web.Support;


using Newtonsoft.Json.Linq;

namespace SIPO.Web.AjaxHandler.Transaction
{

    public class recRegistrationhandler : SIPO.Web.BaseHandler
    {

        private recRegistrationRepo recRegistrationDal = new recRegistrationRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);
        private recSectorRegistrationRepo recSectorRegistrationDal = new recSectorRegistrationRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);
        private recTransactionTypeRepo recTransactionTypeDal = new recTransactionTypeRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);
        private recLogRepo logDal = new recLogRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);
        private recBillingRepo recBillingRepo = new recBillingRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);
        private recInvoiceRepo recInvoiceRepo = new recInvoiceRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);
        private recDebtorRepo recDebtorRepo = new recDebtorRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);
        private recUserSectorRepo recUserSectorDal = new recUserSectorRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);
        recParameterRepo paramDal = new recParameterRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);
        private recDayOffRepo recDayOffRepoDal = new recDayOffRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);
        private string MethodName = string.Empty;

        public override void ProcessRequest(HttpContext context)
        {
            base.ProcessRequest(context);

            MethodName = context.Request.Params["method"];

            context.Response.Clear();
            context.Response.Buffer = true;

            System.IO.StreamReader sr = new System.IO.StreamReader(context.Request.InputStream);

            string jsonString = string.Empty;
            jsonString = context.Server.UrlDecode(sr.ReadToEnd());
            JObject jObj = string.IsNullOrEmpty(jsonString) ? null : JObject.Parse(jsonString);

            sr.Close();
            sr.Dispose();

            switch (MethodName.ToLower())
            {
                case ("insert"):
                    context.Response.Write(Insert(context, jObj));
                    break;
                case ("update"):
                    context.Response.Write(Update(context, jObj));
                    break;
                case ("delete"):
                    context.Response.Write(Delete(context, jObj));
                    break;
                case ("getdata"):
                    context.Response.Write(GetData(context));
                    break;
                case ("setnonactive"):
                    context.Response.Write(SetNonActive(context, jObj));
                    break;
                case ("updatestatusregistration"):
                    context.Response.Write(UpdateStatusRegistration(context, jObj));
                    break;
                case ("updateafterdebtor"):
                    context.Response.Write(UpdateAfterDebtor(context, jObj));
                    break;
                default:
                    // do nothing
                    break;
            }

            context.Response.Flush();
            context.Response.End();
        }

        public override bool IsReusable
        {
            get
            {
                return false;
            }
        }

        private string Insert(HttpContext context, JObject jo)
        {
            JSONHelper helper = new JSONHelper();
            string captchaHash = (string)jo["CaptchaHash"];
            string captcha = (string)jo["Captcha"];
            bool isExpired = string.IsNullOrEmpty(jo["IsExpired"].ToString()) ? false : (jo["IsExpired"].ToString().Equals("1") ? true : false);

            if ((Helper.HashCaptcha(captcha).Equals(captchaHash) || (captcha == "internal")) && (!isExpired)) // Check input captcha = Generated captcha
            {
                try
                {
                    //recTransactionType transType = recTransactionTypeDal.GetByLikeTransTypeName("registrasi");
                    recTransactionType transType = recTransactionTypeDal.GetByTransTypeCode(1);
                    recRegistration obj = new recRegistration();
                    recDebtor debtor = new recDebtor();
                    recUser objUser = new recUser();
                    if (!string.IsNullOrEmpty(base.SubSectorName))
                    {
                        if (base.DebtorID.HasValue)
                        {
                            recDebtor debtors = recDebtorRepo.GetById(base.DebtorID.Value);
                            if (debtor != null)
                            {
                                //recRegistration
                                obj.PreDebtorName = debtors.DebtorName;
                                obj.IsIndividual = debtors.IsIndividual;
                                obj.NPWP = debtors.NPWP;
                                obj.Address1 = debtors.Address1;
                                obj.Address2 = debtors.Address2;
                                obj.Address3 = debtors.Address3;
                                obj.Phone = debtors.Phone;
                                obj.Fax = debtors.Fax;
                                obj.ZipCode = debtors.ZipCode;
                                obj.Website = debtors.Website;
                                obj.Email = debtors.Email;
                                obj.RegionID = debtors.RegionID;
                                obj.CityID = debtors.CityID;

                                //debtor
                                debtor.DebtorName = debtors.DebtorName;
                                debtor.IsIndividual = debtors.IsIndividual;
                                debtor.NPWP = debtors.NPWP;
                                debtor.Address1 = debtors.Address1;
                                debtor.Address2 = debtors.Address2;
                                debtor.Address3 = debtors.Address3;
                                debtor.Phone = debtors.Phone;
                                debtor.Fax = debtors.Fax;
                                debtor.ZipCode = debtors.ZipCode;
                                debtor.Website = debtors.Website;
                                debtor.Email = debtors.Email;
                                debtor.RegionID = debtors.RegionID;
                                debtor.CityID = debtors.CityID;

                                //user
                                objUser.Email = debtors.Email;
                            }
                        }
                        else
                        {
                            if (base.RegistrationID != 0)
                            {
                                recRegistration recRegistration = new recRegistration();
                                recRegistration = recRegistrationDal.GetById(base.RegistrationID);
                                if (recRegistration != null)
                                {
                                    //registration
                                    obj.PreDebtorName = recRegistration.PreDebtorName;
                                    obj.IsIndividual = (bool)recRegistration.IsIndividual;
                                    obj.NPWP = recRegistration.NPWP;
                                    obj.Address1 = recRegistration.Address1;
                                    obj.Address2 = recRegistration.Address2;
                                    obj.Address3 = recRegistration.Address3;
                                    obj.Phone = recRegistration.Phone;
                                    obj.Fax = recRegistration.Fax;
                                    obj.ZipCode = recRegistration.ZipCode;
                                    obj.Website = recRegistration.Website;
                                    obj.Email = recRegistration.Email;
                                    obj.RegionID = recRegistration.RegionID;
                                    obj.CityID = recRegistration.CityID;

                                    //debtor
                                    debtor.DebtorName = recRegistration.PreDebtorName;
                                    debtor.IsIndividual = (bool)recRegistration.IsIndividual;
                                    debtor.NPWP = recRegistration.NPWP;
                                    debtor.Address1 = recRegistration.Address1;
                                    debtor.Address2 = recRegistration.Address2;
                                    debtor.Address3 = recRegistration.Address3;
                                    debtor.Phone = recRegistration.Phone;
                                    debtor.Fax = recRegistration.Fax;
                                    debtor.ZipCode = recRegistration.ZipCode;
                                    debtor.Website = recRegistration.Website;
                                    debtor.Email = recRegistration.Email;
                                    debtor.RegionID = recRegistration.RegionID;
                                    debtor.CityID = recRegistration.CityID;

                                    //user
                                    objUser.Email = recRegistration.Email;
                                }
                            }
                        }
                    }
                    else
                    {
                        //recRegistration
                        obj.PreDebtorName = (string)jo["PreDebtorName"];
                        obj.IsIndividual = (bool)jo["IsIndividual"];
                        obj.NPWP = (string)jo["NPWP"];
                        if (jo["Address1"].ToString() != "")
                            obj.Address1 = (string)jo["Address1"];
                        if (jo["Address2"].ToString() != "")
                            obj.Address2 = (string)jo["Address2"];
                        if (jo["Address3"].ToString() != "")
                            obj.Address3 = (string)jo["Address3"];
                        if (jo["ZipCode"].ToString() != "")
                            obj.ZipCode = (string)jo["ZipCode"];
                        obj.Email = (string)jo["Email"];
                        if (jo["Phone"].ToString() != "")
                            obj.Phone = (string)jo["Phone"];
                        if (jo["Fax"].ToString() != "")
                            obj.Fax = (string)jo["Fax"];
                        obj.Website = (string)jo["Website"];
                        if (jo["RegionID"].ToString() != "")
                            obj.RegionID = Convert.ToInt32(jo["RegionID"]);
                        if (jo["CityID"].ToString() != "")
                            obj.CityID = Convert.ToInt32(jo["CityID"]);

                        //debtor
                        debtor.DebtorName = (string)jo["PreDebtorName"];
                        debtor.NPWP = (string)jo["NPWP"];
                        if (jo["Address1"].ToString() != "")
                            debtor.Address1 = (string)jo["Address1"];
                        if (jo["Address2"].ToString() != "")
                            debtor.Address2 = (string)jo["Address2"];
                        if (jo["Address3"].ToString() != "")
                            debtor.Address3 = (string)jo["Address3"];
                        if (jo["ZipCode"].ToString() != "")
                            debtor.ZipCode = (string)jo["ZipCode"];
                        debtor.Email = (string)jo["Email"];
                        if (jo["Phone"].ToString() != "")
                            debtor.Phone = (string)jo["Phone"];
                        if (jo["Fax"].ToString() != "")
                            debtor.Fax = (string)jo["Fax"];
                        debtor.Website = (string)jo["Website"];
                        if (jo["RegionID"].ToString() != "")
                            debtor.RegionID = Convert.ToInt32(jo["RegionID"]);
                        if (jo["CityID"].ToString() != "")
                            debtor.CityID = Convert.ToInt32(jo["CityID"]);
                    }

                    obj.CreatedDate = DateTime.Now;
                    obj.CreatedBy = base.UserLoggedIn;
                    obj.IsActive = true;

                    debtor.UpdatedDate = DateTime.Now;
                    debtor.UpdatedBy = base.UserLoggedIn;

                    recSectorRegistration objSectorRegistration = new recSectorRegistration();
                    objSectorRegistration.SectorID = Convert.ToInt32(jo["SectorID"]);
                    objSectorRegistration.SubSectorID = Convert.ToInt32(jo["SubSectorID"]);
                    objSectorRegistration.SubSubSectorID = Convert.ToInt32(jo["SubSubSectorID"]);
                    objSectorRegistration.IsApproved = false;
                    //objSectorRegistration.InvoiceNo = "REG" + DateTime.Now.ToString("MM") + DateTime.Now.ToString("yy") + recInvoiceRepo.GetMaxID().ToString().PadLeft(5, '0');
                    objSectorRegistration.InvoiceNo = (string)jo["SectorCode"] + "." + (string)jo["SubSectorCode"] + "." + DateTime.Now.ToString("MM") + "." + DateTime.Now.ToString("yy") + "." + recInvoiceRepo.GetMaxID((string)jo["SectorCode"], (string)jo["SubSectorCode"]).ToString().PadLeft(5, '0');
                    objSectorRegistration.InvoiceDate = DateTime.Now;
                    objSectorRegistration.InvoiceValue = Convert.ToDecimal(jo["InvoiceValue"]);
                    if (jo["EmisionTypeID"].ToString() != "")
                        objSectorRegistration.EmisionTypeID = (short)(jo["EmisionTypeID"]);
                    if (jo["EmisionValue"].ToString() != "")
                    {
                        //objSectorRegistration.EmisionValue = Convert.ToDecimal(jo["EmisionValue"].ToString().Replace(".",""));
                        objSectorRegistration.EmisionValue = Helper.ToDecimal(jo["EmisionValue"].ToString(), System.Globalization.NumberStyles.Number, Helper.Culture.Indonesian);
                    }
                    objSectorRegistration.RegistrationStatusID = 1;
                    objSectorRegistration.RegistrationDate = DateTime.Now;
                    objSectorRegistration.CreatedDate = DateTime.Now;
                    objSectorRegistration.CreatedBy = base.UserLoggedIn;
                    if (jo["RegistrationEmisionTypeID"].ToString() != "")
                        objSectorRegistration.RegistrationEmisionTypeID = (short)(jo["RegistrationEmisionTypeID"]);

                    //user
                    if (!string.IsNullOrEmpty(base.SubSectorName))
                    {
                        recUserRepo recUserDal = new recUserRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);
                        recUser user = recUserDal.GetByUserCode(base.UserLoggedIn);
                        if (user != null)
                        {
                            objUser.UserCode = user.UserCode;
                            objUser.UserName = user.UserCode;
                            objUser.Password = user.Password;
                        }
                    }
                    else
                    {
                        objUser.UserCode = (string)jo["UserCode"];
                        objUser.UserName = (string)jo["UserCode"];
                        objUser.Password = Helper.CreatePasswordHash((string)jo["Password"]);
                        objUser.Email = (string)jo["Email"];
                    }
                    objUser.IsActive = true;
                    objUser.CreatedDate = DateTime.Now;
                    objUser.CreatedBy = base.UserLoggedIn;
                    objUser.UserTypeID = 1;

                    recUserSector objUserSector = new recUserSector();
                    objUserSector.SubSubSectorID = Convert.ToInt32(jo["SubSubSectorID"]);
                    objUserSector.UserTypeID = 1;
                    objUserSector.CreatedDate = DateTime.Now;
                    objUserSector.CreatedBy = base.UserLoggedIn;

                    recInvoice objInvoice = new recInvoice();
                    objInvoice.InvoiceNo = (string)jo["SectorCode"] + "." + (string)jo["SubSectorCode"] + "." + DateTime.Now.ToString("MM") + "." + DateTime.Now.ToString("yy") + "." + recInvoiceRepo.GetMaxID((string)jo["SectorCode"], (string)jo["SubSectorCode"]).ToString().PadLeft(5, '0');//"REG" + DateTime.Now.ToString("MM") + DateTime.Now.ToString("yy") + recInvoiceRepo.GetMaxID().ToString().PadLeft(5, '0');
                    //objInvoice.PeriodYear = DateTime.Now.Year;
                    //objInvoice.PeriodMonth = (short)DateTime.Now.Month;
                    //objInvoice.TransDate = DateTime.Now;
                    objInvoice.InvoiceDate = DateTime.Now;
                    objInvoice.InvoiceValue = Convert.ToDecimal(jo["InvoiceValue"]);
                    objInvoice.Balance = Convert.ToDecimal(jo["InvoiceValue"]);

                    //ambil bank ID dari subsector

                    recSubSectorRepo recSubSectorDal = new recSubSectorRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);
                    recSubSector objSubSector = recSubSectorDal.GetBySubSectorCode(Convert.ToInt32((string)jo["SubSectorCode"]), Convert.ToInt32(jo["SectorID"]));
                    if (objSubSector != null)
                    {
                        objInvoice.BankID = (short)objSubSector.BankID;
                    }

                    //end
                    
                    //objInvoice.BankID = (short)transType.BankID;
                    objInvoice.CreatedDate = DateTime.Now;
                    objInvoice.CreatedBy = base.UserLoggedIn;
                    if (jo["BeSpelled"].ToString() != "")
                        objInvoice.BeSpelled = (string)jo["BeSpelled"];

                    recInvoiceDetail objInvoiceDetail = new recInvoiceDetail();
                    objInvoiceDetail.InvoiceNo = (string)jo["SectorCode"] + "." + (string)jo["SubSectorCode"] + "." + DateTime.Now.ToString("MM") + "." + DateTime.Now.ToString("yy") + "." + recInvoiceRepo.GetMaxID((string)jo["SectorCode"], (string)jo["SubSectorCode"]).ToString().PadLeft(5, '0');//"REG" + DateTime.Now.ToString("MM") + DateTime.Now.ToString("yy") + recInvoiceRepo.GetMaxID().ToString().PadLeft(5, '0');
                    objInvoiceDetail.Value = Convert.ToDecimal(jo["InvoiceValue"]);
                    objInvoiceDetail.TariffID = Convert.ToInt32(jo["TarifID"]);
                    objInvoiceDetail.TransTypeID = (short)transType.TransTypeID;
                    objInvoiceDetail.CreatedDate = DateTime.Now;
                    objInvoiceDetail.CreatedBy = base.UserLoggedIn;
                    //objInvoiceDetail.BillingNo = "1." + DateTime.Now.ToString("MM") + "." + DateTime.Now.ToString("yy") + "." + recBillingRepo.GetMaxID("1").ToString().PadLeft(5, '0');//Guid.NewGuid().ToString().Substring(0, 9);
                    objInvoiceDetail.BillingNo = recBillingRepo.GenerateBillingNoSP("1");

                    if (jo["RegistrationLabelName"].ToString() != "")
                        if (jo["RegistrationEmisionTypeID"].ToString() != "")
                        {
                            if (jo["SubSectorName"].ToString().ToLower() == "emiten" || jo["SubSectorName"].ToString().ToLower() == "perusahaan publik")
                            {
                                if (jo["EmisionTypeID"].ToString() != "")
                                    objInvoiceDetail.ForPayment = "Pernyataan " + (string)jo["RegistrationLabelName"] + "-" + (string)jo["RegistrationEmisionTypeText"] + "-" + (string)jo["EmisionTypeText"];
                                else
                                    objInvoiceDetail.ForPayment = "Pernyataan " + (string)jo["RegistrationLabelName"] + "-" + (string)jo["RegistrationEmisionTypeText"];
                            }
                        }
                        else
                            objInvoiceDetail.ForPayment = (string)jo["RegistrationLabelName"] + "-" + (string)jo["SubSectorName"] + "-" + (string)jo["SubSubSectorName"];
                    else
                    {
                        if (jo["RegistrationEmisionTypeID"].ToString() != "")
                        {
                            if (jo["SubSectorName"].ToString().ToLower() == "emiten" || jo["SubSectorName"].ToString().ToLower() == "perusahaan publik")
                            {
                                if (jo["EmisionTypeID"].ToString() != "")
                                    objInvoiceDetail.ForPayment = "Pernyataan " + (string)jo["RegistrationEmisionTypeText"] + "-" + (string)jo["EmisionTypeText"];
                                else
                                    objInvoiceDetail.ForPayment = "Pernyataan " + (string)jo["RegistrationEmisionTypeText"];
                            }
                        }
                        else
                            objInvoiceDetail.ForPayment = (string)jo["SubSectorName"] + "-" + (string)jo["SubSubSectorName"];
                    }
                    objInvoiceDetail.DueDate = recDayOffRepoDal.GetDueDate(DateTime.Now.AddDays(Convert.ToDouble(transType.TermDay)).ToString("yyyyMMdd"));

                    recBilling objBilling = new recBilling();
                    objBilling.TransTypeID = (short)transType.TransTypeID;
                    objBilling.TariffID = Convert.ToInt32(jo["TarifID"]);
                    objBilling.Value = Convert.ToDecimal(jo["InvoiceValue"]);
                    objBilling.Balance = Convert.ToDecimal(jo["InvoiceValue"]);
                    objBilling.InvoiceNo = (string)jo["SectorCode"] + "." + (string)jo["SubSectorCode"] + "." + DateTime.Now.ToString("MM") + "." + DateTime.Now.ToString("yy") + "." + recInvoiceRepo.GetMaxID((string)jo["SectorCode"], (string)jo["SubSectorCode"]).ToString().PadLeft(5, '0');//"REG" + DateTime.Now.ToString("MM") + DateTime.Now.ToString("yy") + recInvoiceRepo.GetMaxID().ToString().PadLeft(5, '0');
                    //objBilling.BillingNo = "1." + DateTime.Now.ToString("MM") + "." + DateTime.Now.ToString("yy") + "." + recBillingRepo.GetMaxID("1").ToString().PadLeft(5, '0');//Guid.NewGuid().ToString().Substring(0, 9);
                    objBilling.BillingNo = recBillingRepo.GenerateBillingNoSP("1");
                    objBilling.BillingDate = DateTime.Now;

                    objBilling.DueDate = recDayOffRepoDal.GetDueDate(DateTime.Now.AddDays(Convert.ToDouble(transType.TermDay)).ToString("yyyyMMdd"));
                    objBilling.CreatedDate = DateTime.Now;
                    objBilling.CreatedBy = base.UserLoggedIn;
                    if (jo["RegistrationLabelName"].ToString() != "")
                        objBilling.ForPayment = (string)jo["RegistrationLabelName"] + "-" + (string)jo["SubSectorName"] + "-" + (string)jo["SubSubSectorName"];
                    else
                        objBilling.ForPayment = (string)jo["SubSectorName"] + "-" + (string)jo["SubSubSectorName"];

                    recUserRole objUserRole = new recUserRole();
                    objUserRole.RoleID = Convert.ToInt32(paramDal.GetById("CalonDebiturRole").Value); //3;
                    objUserRole.CreatedBy = base.UserLoggedIn;
                    objUserRole.CreatedDate = DateTime.Now;

                    recRegistration registrationByNPWP = new recRegistration();
                    recDebtor debtorByNPWP = new recDebtor();
                    recDebtor debtorByNPWPUnion = new recDebtor();
                    recUser recUser = new recUser();
                    recUserRepo recUserRepo = new recUserRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);

                    registrationByNPWP = recRegistrationDal.GetByNPWP(obj.NPWP);
                    debtorByNPWPUnion = recDebtorRepo.GetByNPWPUnion(obj.NPWP);
                    debtorByNPWP = recDebtorRepo.GetByNPWP(obj.NPWP);
                    recUser = recUserRepo.GetByUserCode(objUser.UserCode.Trim());
                    if (debtorByNPWPUnion == null && recUser == null)
                    {
                        //simpan ke registration,sectorregistration,user,usersector,invoice,invoicedetail,billing
                        recRegistrationDal.Save(obj, objSectorRegistration, objUser, objUserSector, objInvoice, objInvoiceDetail, objBilling, objUserRole);
                    }
                    else if (debtorByNPWPUnion != null && recUser != null)
                    {
                        objUserSector.UserID = recUser.UserID;
                        //update registration, simpan ke registration (jika npwp belum ada),sectorregistration,usersector,invoice,invoicedetail,billing
                        if (registrationByNPWP != null)
                        {
                            objSectorRegistration.RegistrationID = registrationByNPWP.RegistrationID;
                            obj.RegistrationID = registrationByNPWP.RegistrationID;
                            recRegistrationDal.SaveAndUpdate(obj, objSectorRegistration, objUserSector, objInvoice, objInvoiceDetail, objBilling);
                        }
                        else
                        {
                            //simpan ke registration,sectorregistration,usersector,invoice,invoicedetail,billing
                            recRegistrationDal.Save(obj, objSectorRegistration, objUserSector, objInvoice, objInvoiceDetail, objBilling);
                        }
                        if (debtorByNPWP != null)
                        {
                            //update debtor,simpan ke sectorregistration,usersector,invoice,invoicedetail,billing
                            debtor.DebtorID = debtorByNPWP.DebtorID;
                            //recRegistrationDal.Save(debtor, objSectorRegistration, objUserSector, objInvoice, objInvoiceDetail, objBilling);
                            recRegistrationDal.Update(debtor);
                        }

                    }
                    else if (debtorByNPWPUnion != null && recUser == null)
                    {
                        //update registration, simpan ke se registration (jika npwp belum ada),sectorregistration,user,usersector,invoice,invoicedetail,billing
                        if (registrationByNPWP != null)
                        {
                            objSectorRegistration.RegistrationID = registrationByNPWP.RegistrationID;
                            obj.RegistrationID = registrationByNPWP.RegistrationID;
                            recRegistrationDal.SaveAndUpdate(obj, objSectorRegistration, objUser, objUserSector, objInvoice, objInvoiceDetail, objBilling, objUserRole);
                        }
                        else
                        {
                            recRegistrationDal.Save(obj, objSectorRegistration, objUser, objUserSector, objInvoice, objInvoiceDetail, objBilling, objUserRole);
                        }
                        if (debtorByNPWP != null)
                        {
                            debtor.DebtorID = debtorByNPWP.DebtorID;
                            recRegistrationDal.Update(debtor);
                        }

                    }
                    helper.Message = "Data berhasil di simpan dan telah terkirim ke email !";
                    helper.Data = objInvoice.InvoiceNo;
                    helper.IsSuccess = true;
                }
                catch (Exception ex)
                {
                    helper.IsSuccess = false;
                    helper.Message = ex.Message;
                }
                finally
                {
                    recLog log = new recLog();
                    log.LogEvent = "Insert";
                    log.LogMessage = helper.Message;
                    log.LogSource = "srecRegistrationhandler.ashx";
                    log.CreatedBy = base.UserLoggedIn;
                    log.CreatedDate = DateTime.Now;
                    logDal.Save(log);

                }
            }
            else
            {
                helper.IsSuccess = false;

                if (isExpired)
                    helper.Message = "Captcha telah habis masa waktunya, silahkan masukan yang baru !";
                else
                    helper.Message = "Anda salah memasukkan Captcha !";

            }
            return Newtonsoft.Json.JsonConvert.SerializeObject(helper);

        }

        private string UpdateAfterDebtor(HttpContext context, JObject jo)
        {
            JSONHelper helper = new JSONHelper();

            try
            {
                recDebtor obj = new recDebtor();
                //obj.DebtorID = Convert.ToInt32(jo["DebtorId"]);
                int perorangan = Convert.ToInt32(jo["IsPerorangan"]);
                obj.DebtorName = (string)jo["PreDebtorName"];
                if (perorangan == 1)
                {
                    obj.DebtorCode = "1" + recDebtorRepo.GetMaxID(perorangan).ToString().PadLeft(7, '0');
                    obj.IsIndividual = true;
                }
                else
                {
                    obj.DebtorCode = "2" + recDebtorRepo.GetMaxID(perorangan).ToString().PadLeft(7, '0');
                    obj.IsIndividual = false;
                }
                obj.NPWP = (string)jo["NPWP"];
                if (jo["Address1"].ToString() != "")
                    obj.Address1 = (string)jo["Address1"];
                if (jo["Address2"].ToString() != "")
                    obj.Address2 = (string)jo["Address2"];
                if (jo["Address3"].ToString() != "")
                    obj.Address3 = (string)jo["Address3"];
                if (jo["ZipCode"].ToString() != "")
                    obj.ZipCode = (string)jo["ZipCode"];
                obj.Email = (string)jo["Email"];
                if (jo["Phone"].ToString() != "")
                    obj.Phone = (string)jo["Phone"];
                if (jo["Fax"].ToString() != "")
                    obj.Fax = (string)jo["Fax"];
                obj.Website = (string)jo["Website"];
                if (jo["RegionID"].ToString() != "")
                    obj.RegionID = Convert.ToInt32(jo["RegionID"]);
                if (jo["CityID"].ToString() != "")
                    obj.CityID = Convert.ToInt32(jo["CityID"]);
                obj.UpdatedDate = DateTime.Now;
                obj.UpdatedBy = base.UserLoggedIn;
                obj.CreatedDate = DateTime.Now;
                obj.CreatedBy = base.UserLoggedIn;
                obj.IsActive = true;

                string captcha = jo["Captcha"].ToString();
                string captchaHash = jo["CaptchaHash"].ToString();
                bool isExpired = string.IsNullOrEmpty(jo["IsExpired"].ToString()) ? false : (jo["IsExpired"].ToString().Equals("1") ? true : false);

                if (Helper.HashCaptcha(captcha).Equals(captchaHash) && !isExpired)
                {
                    recDebtorSector objDebtorSector = new recDebtorSector();
                    //objDebtorSector.DebtorID = obj.DebtorID;
                    objDebtorSector.SectorID = Convert.ToInt32(jo["SectorID"]);
                    objDebtorSector.SubSectorID = Convert.ToInt32(jo["SubSectorID"]);
                    objDebtorSector.SubSubSectorID = Convert.ToInt32(jo["SubSubSectorID"]);
                    objDebtorSector.IsActive = true;
                    objDebtorSector.CreatedDate = DateTime.Now;
                    objDebtorSector.CreatedBy = base.UserLoggedIn;

                    recUser objUser = new recUser();
                    objUser.UserCode = (string)jo["UserCode"];
                    objUser.UserName = (string)jo["UserCode"];
                    objUser.Password = Helper.CreatePasswordHash((string)jo["Password"]);
                    objUser.IsActive = true;
                    objUser.Email = (string)jo["Email"];
                    objUser.CreatedDate = DateTime.Now;
                    objUser.CreatedBy = base.UserLoggedIn;
                    objUser.UserTypeID = 2;

                    recUserSector objUserSector = new recUserSector();
                    objUserSector.SubSubSectorID = Convert.ToInt32(jo["SubSubSectorID"]);
                    objUserSector.UserTypeID = 2;
                    objUserSector.CreatedDate = DateTime.Now;
                    objUserSector.CreatedBy = base.UserLoggedIn;

                    recUserRole objUserRole = new recUserRole();
                    string subsectorname = jo["SubSectorName"].ToString().ToLower();
                    if (subsectorname == "emiten" || subsectorname == "perusahaan publik")
                        objUserRole.RoleID = Convert.ToInt32(paramDal.GetById("DebiturEmitenRole").Value); //simpan role sebagai debitur emiten;
                    else
                        objUserRole.RoleID = Convert.ToInt32(paramDal.GetById("DebiturRole").Value); //simpan role sebagai debitur;
                    objUserRole.CreatedBy = base.UserLoggedIn;
                    objUserRole.CreatedDate = DateTime.Now;

                    recDebtor debtor = new recDebtor();
                    recDebtor debtorByNPWP = new recDebtor();
                    recUser recUser = new recUser();
                    recUserRepo recUserRepo = new recUserRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);
                    //debtor = recDebtorRepo.CheckUser(obj.NPWP, objUser.UserCode);
                    debtorByNPWP = recDebtorRepo.GetByNPWP(obj.NPWP);
                    recUser = recUserRepo.GetByUserCode(objUser.UserCode.Trim());

                    if (debtorByNPWP != null && recUser != null)
                    {
                        //update debtor, simpan ke debtorsector,usersector
                        objDebtorSector.DebtorID = debtorByNPWP.DebtorID;
                        obj.DebtorID = debtorByNPWP.DebtorID;
                        objUserSector.UserID = recUser.UserID;
                        objUserRole.UserID = recUser.UserID;
                        recUserRoleRepo recUserRoleDal = new recUserRoleRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);
                        recUserRole role = recUserRoleDal.GetByUserId(recUser.UserID);
                        if (role.RoleID == Convert.ToInt32(paramDal.GetById("DebiturEmitenRole").Value))
                            recRegistrationDal.SaveAndUpdate(obj, objDebtorSector, objUserSector);
                        else
                            recRegistrationDal.SaveAndUpdate(obj, objDebtorSector, objUserSector, objUserRole);
                    }
                    else if (debtorByNPWP != null && recUser == null)
                    {
                        //update debtor, simpan ke debtorsector,user,usersector,userrole
                        objDebtorSector.DebtorID = debtorByNPWP.DebtorID;
                        obj.DebtorID = debtorByNPWP.DebtorID;
                        recRegistrationDal.SaveAndUpdate(obj, objDebtorSector, objUser, objUserSector, objUserRole);
                    }
                    else if (debtorByNPWP == null && recUser == null)
                    {
                        //simpan ke debtor,debtorsector,user,usersector,userrole
                        recRegistrationDal.Save(obj, objDebtorSector, objUser, objUserSector, objUserRole);
                    }
                    //recRegistrationDal.Update(obj, objDebtorSector, objUser, objUserSector);

                    helper.Message = "Data berhasil di simpan !";
                    helper.IsSuccess = true;
                }
                else
                {
                    helper.IsSuccess = false;

                    if (isExpired)
                        helper.Message = "Captcha telah habis masa waktunya, silahkan masukan yang baru !";
                    else
                        helper.Message = "Anda salah memasukkan Captcha !";

                }
            }
            catch (Exception ex)
            {
                helper.IsSuccess = false;
                helper.Message = ex.Message;
            }
            finally
            {
                recLog log = new recLog();
                log.LogEvent = "Insert";
                log.LogMessage = helper.Message;
                log.LogSource = "srecRegistrationhandler.ashx";
                log.CreatedBy = base.UserLoggedIn;
                log.CreatedDate = DateTime.Now;
                logDal.Save(log);

            }

            return Newtonsoft.Json.JsonConvert.SerializeObject(helper);

        }
        private string Update(HttpContext context, JObject jo)
        {
            JSONHelper helper = new JSONHelper();

            try
            {
                recRegistration obj = new recRegistration();
                obj.RegistrationID = (int)jo["RegistrationID"];
                obj.PreDebtorName = (string)jo["PreDebtorName"];
                obj.NPWP = (string)jo["NPWP"];
                if (jo["Address1"].ToString() != "")
                    obj.Address1 = (string)jo["Address1"];
                if (jo["Address2"].ToString() != "")
                    obj.Address2 = (string)jo["Address2"];
                if (jo["Address3"].ToString() != "")
                    obj.Address3 = (string)jo["Address3"];
                if (jo["ZipCode"].ToString() != "")
                    obj.ZipCode = (string)jo["ZipCode"];
                obj.Email = (string)jo["Email"];
                if (jo["Phone"].ToString() != "")
                    obj.Phone = (string)jo["Phone"];
                if (jo["Fax"].ToString() != "")
                    obj.Fax = (string)jo["Fax"];
                obj.Website = (string)jo["Website"];
                if (jo["RegionID"].ToString() != "")
                    obj.RegionID = Convert.ToInt32(jo["RegionID"]);
                if (jo["CityID"].ToString() != "")
                    obj.CityID = Convert.ToInt32(jo["CityID"]);
                obj.IsActive = (bool)jo["IsActive"];
                obj.UpdatedDate = DateTime.Now;
                obj.UpdatedBy = base.UserLoggedIn;

                recRegistrationDal.Update(obj);

                helper.Message = "Data berhasil di ubah !";
                helper.IsSuccess = true;
            }
            catch (Exception ex)
            {
                helper.IsSuccess = false;
                helper.Message = ex.Message;
            }
            finally
            {
                recLog log = new recLog();
                log.LogEvent = "Update";
                log.LogMessage = helper.Message;
                log.LogSource = "srecRegistrationhandler.ashx";
                log.CreatedBy = base.UserLoggedIn;
                log.CreatedDate = DateTime.Now;
                logDal.Save(log);
            }

            return Newtonsoft.Json.JsonConvert.SerializeObject(helper);

        }
        private string UpdateStatusRegistration(HttpContext context, JObject jo)
        {
            JSONHelper helper = new JSONHelper();

            try
            {
                string registrationStatus = (string)jo["RegistrationStatusValue"];
                recSectorRegistration obj = new recSectorRegistration();
                obj.RegistrationID = (int)jo["RegistrationID"];
                string PaymentNo = DateTime.Now.ToString(); // hanya untuk sementara, format belum ditentukan 
                PaymentNo = PaymentNo.Replace("/", "");
                PaymentNo = PaymentNo.Replace(":", "");
                PaymentNo = PaymentNo.Remove(13);

                jo["PaymentNo"] = PaymentNo;

                obj.PaymentNo = (string)jo["PaymentNo"];

                obj.PaymentValue = Helper.ToDecimal(jo["PaymentValue"].ToString(), System.Globalization.NumberStyles.Number, Helper.Culture.Indonesian);

                obj.PaymentDate = Helper.FormatDate(jo["PaymentDate"].ToString(), Helper.Culture.Indonesian);

                obj.RegistrationStatusID = Convert.ToInt32(jo["RegistrationStatusID"]);
                obj.SectorRegistrationID = Convert.ToInt32(jo["SectorRegistrationID"]);
                obj.UpdatedDate = DateTime.Now;
                obj.UpdatedBy = base.UserLoggedIn;

                recPayment objPayment = new recPayment();
                objPayment.PaymentDate = Helper.FormatDate(jo["PaymentDate"].ToString(), Helper.Culture.Indonesian);
                objPayment.InvoiceNo = (string)jo["InvoiceNo"];

                objPayment.PaymentValue = Helper.ToDecimal(jo["PaymentValue"].ToString(), System.Globalization.NumberStyles.Number, Helper.Culture.Indonesian);

                objPayment.CreatedDate = DateTime.Now;
                objPayment.CreatedBy = base.UserLoggedIn;
                objPayment.TransDate = DateTime.Now;
                objPayment.PaymentNo = (string)jo["PaymentNo"];

                if (registrationStatus.ToLower().Contains("terima"))
                {
                    obj.IsApproved = true;
                    obj.ApprovedBy = base.UserLoggedIn;
                    obj.ApprovedDate = DateTime.Now;
                }
                else if (registrationStatus.ToLower().Contains("tolak"))
                {
                    obj.IsApproved = false;
                    obj.ApprovedBy = base.UserLoggedIn;
                    obj.ApprovedDate = DateTime.Now;
                }

                recInvoice objInvoice = new recInvoice();
                objInvoice.PaymentNo = (string)jo["PaymentNo"];

                objInvoice.PaymentValue = Helper.ToDecimal(jo["PaymentValue"].ToString(), System.Globalization.NumberStyles.Number, Helper.Culture.Indonesian);
                objInvoice.PaymentDate = Helper.FormatDate(jo["PaymentDate"].ToString(), Helper.Culture.Indonesian);
                objInvoice.InvoiceNo = (string)jo["InvoiceNo"];
                objInvoice.UpdatedDate = DateTime.Now;
                objInvoice.UpdatedBy = base.UserLoggedIn;

                recUserSector objUserSector = new recUserSector();
                objUserSector.RegisterSectorID = Convert.ToInt32(jo["SectorRegistrationID"]);
                objUserSector.UserTypeID = 1;
                if (registrationStatus.ToLower().Contains("terima"))
                    objUserSector.UserTypeID = 2;
                objUserSector.UpdatedDate = DateTime.Now;
                objUserSector.UpdatedBy = base.UserLoggedIn;

                if (registrationStatus.ToLower().Contains("sedang"))
                    recRegistrationDal.Update(obj, objInvoice, objUserSector, objPayment);
                else if (registrationStatus.ToLower().Contains("tolak"))
                    recRegistrationDal.Update(obj, objInvoice, objUserSector);
                else if (registrationStatus.ToLower().Contains("terima"))
                {
                    recRegistration registration = recRegistrationDal.GetByIdCopyToDebtor(obj.RegistrationID);
                    recSectorRegistration sectorRegistration = recSectorRegistrationDal.GetById(Convert.ToInt32(jo["SectorRegistrationID"]));
                    if (registration != null)
                    {
                        recDebtor debtor = new recDebtor();
                        bool? isPerorangan = registration.IsIndividual;
                        bool isIndividual;
                        int perorangan;
                        if (isPerorangan == true)
                        {
                            perorangan = 1;
                            isIndividual = true;
                            debtor.DebtorCode = "1" + recDebtorRepo.GetMaxID(perorangan).ToString().PadLeft(7, '0');
                            //debtor.DebtorCode = "1" + Helper.String.Digits(recDebtorRepo.GetMaxID(perorangan).ToString(), 7);
                        }
                        else
                        {
                            perorangan = 2;
                            isIndividual = false;
                            debtor.DebtorCode = "2" + recDebtorRepo.GetMaxID(perorangan).ToString().PadLeft(7, '0');
                            //debtor.DebtorCode = "2" + Helper.String.Digits(recDebtorRepo.GetMaxID(perorangan).ToString(), 7);
                        }
                        debtor.IsIndividual = isIndividual;
                        debtor.DebtorName = registration.PreDebtorName;
                        debtor.NPWP = registration.NPWP;
                        debtor.Address1 = registration.Address1;
                        debtor.Address2 = registration.Address2;
                        debtor.Address3 = registration.Address3;
                        debtor.RegionID = registration.RegionID;
                        debtor.CityID = registration.CityID;
                        debtor.Phone = registration.Phone;
                        debtor.ZipCode = registration.ZipCode;
                        debtor.Email = registration.Email;
                        debtor.Fax = registration.Fax;
                        debtor.Website = registration.Website;
                        debtor.CreatedBy = base.UserLoggedIn;
                        debtor.CreatedDate = DateTime.Now;
                        debtor.IsActive = true;

                        recDebtorSector debtorSector = new recDebtorSector();
                        if (sectorRegistration != null)
                        {
                            debtorSector.SectorID = sectorRegistration.SectorID;
                            debtorSector.SubSectorID = sectorRegistration.SubSectorID;
                            debtorSector.SubSubSectorID = sectorRegistration.SubSubSectorID;
                        }
                        debtorSector.IsActive = true;
                        debtorSector.CreatedBy = base.UserLoggedIn;
                        debtorSector.CreatedDate = DateTime.Now;

                        recBilling recBilling = new recBilling();
                        recBilling = recBillingRepo.GetByInvoiceNo((string)jo["InvoiceNo"]);
                        recBilling billing = new recBilling();
                        billing.InvoiceNo = (string)jo["InvoiceNo"];
                        billing.UpdatedDate = DateTime.Now;
                        billing.UpdatedBy = base.UserLoggedIn;
                        if (recBilling != null)
                        {
                            billing.Balance = recBilling.Value - Helper.ToDecimal(jo["PaymentValue"].ToString(), System.Globalization.NumberStyles.Number, Helper.Culture.Indonesian);
                            objInvoice.Balance = recBilling.Value - Helper.ToDecimal(jo["PaymentValue"].ToString(), System.Globalization.NumberStyles.Number, Helper.Culture.Indonesian);
                        }

                        recUser objUser = new recUser();
                        recUserRole objUserRole = new recUserRole();
                        recUserSector objUserSectorGetbyId = new recUserSector();
                        objUserSectorGetbyId = recUserSectorDal.GetByRegisterSectorID(Convert.ToInt32(jo["SectorRegistrationID"]));
                        recUserRole role = new recUserRole();
                        if (objUserSectorGetbyId != null)
                        {
                            objUser.UserID = objUserSectorGetbyId.UserID;
                            objUserRole.UserID = objUserSectorGetbyId.UserID;
                            recUserRoleRepo recUserRoleDal = new recUserRoleRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);
                            role = recUserRoleDal.GetByUserId((int)objUserRole.UserID);
                        }
                        objUser.UserTypeID = 2;
                        objUser.UpdatedBy = base.UserLoggedIn;
                        objUser.UpdatedDate = DateTime.Now;
                        recSubSectorRepo recSubSectorDal = new recSubSectorRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);
                        recSubSector subSector = recSubSectorDal.GetById(sectorRegistration.SubSectorID);
                        string subSectorName = string.Empty;
                        if (subSector != null)
                            subSectorName = subSector.SubSectorName.ToLower();
                        if (subSectorName == "emiten" || subSectorName == "perusahaan publik")
                            objUserRole.RoleID = Convert.ToInt32(paramDal.GetById("DebiturEmitenRole").Value); //2; update userrole=debitur emiten
                        else
                            objUserRole.RoleID = Convert.ToInt32(paramDal.GetById("DebiturRole").Value); //2; update userrole=debitur
                        if (role.RoleID == Convert.ToInt32(paramDal.GetById("DebiturEmitenRole").Value))
                            objUserRole.RoleID = Convert.ToInt32(paramDal.GetById("DebiturEmitenRole").Value); //2; update userrole=debitur emiten 
                        recDebtorSectorRepo recDebtorSectorDal = new recDebtorSectorRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);
                        recDebtor debtorByNPWP = new recDebtor();
                        recDebtorSector debtorByNPWPAndSector = new recDebtorSector();
                        debtorByNPWPAndSector = recDebtorSectorDal.GetByNPWPAndSector(debtor.NPWP, debtorSector.SectorID, debtorSector.SubSectorID, debtorSector.SubSubSectorID);
                        debtorByNPWP = recDebtorRepo.GetByNPWP(debtor.NPWP);

                         
                        if (debtorByNPWP == null)
                            recRegistrationDal.Update(debtor, debtorSector, obj, objInvoice, objUserSector, billing, objUser, objUserRole);
                        else
                        {
                            if (debtorByNPWPAndSector == null)
                            {
                                debtorSector.DebtorID = debtorByNPWP.DebtorID;
                                recRegistrationDal.Update(debtorSector, obj, objInvoice, objUserSector, billing, objUser, objUserRole);
                            }
                            else
                            {
                                obj.DebtorSectorID = debtorByNPWPAndSector.DebtorSectorID;
                                objInvoice.DebtorSectorID = debtorByNPWPAndSector.DebtorSectorID;
                                objUserSector.DebtorSectorID = debtorByNPWPAndSector.DebtorSectorID;
                                billing.DebtorSectorID = debtorByNPWPAndSector.DebtorSectorID;

                                recRegistrationDal.Update(obj, objInvoice, objUserSector, billing);
                            }
                        }

                    }
                }

                helper.Message = "Data berhasil di ubah !";
                helper.IsSuccess = true;
            }
            catch (Exception ex)
            {
                helper.IsSuccess = false;
                helper.Message = ex.Message;
            }
            finally
            {
                recLog log = new recLog();
                log.LogEvent = "UpdateStatusRegistration";
                log.LogMessage = helper.Message;
                log.LogSource = "srecRegistrationhandler.ashx";
                log.CreatedBy = base.UserLoggedIn;
                log.CreatedDate = DateTime.Now;
                logDal.Save(log);
            }

            return Newtonsoft.Json.JsonConvert.SerializeObject(helper);

        }

        private string Delete(HttpContext context, JObject jo)
        {
            JSONHelper helper = new JSONHelper();

            try
            {
                var strId = (string)jo["RegistrationID"];

                string[] ids = strId.Split(',');
                foreach (string id in ids)
                {
                    int intID = Convert.ToInt32(id);
                    recRegistrationDal.Delete(intID);
                }

                helper.IsSuccess = true;
                helper.Message = "Data berhasil di hapus !";
            }
            catch (Exception ex)
            {
                helper.IsSuccess = false;
                helper.Message = ex.Message;
            }
            finally
            {
                recLog log = new recLog();
                log.LogEvent = "Delete";
                log.LogMessage = helper.Message;
                log.LogSource = "srecRegistrationhandler.ashx";
                log.CreatedBy = base.UserLoggedIn;
                log.CreatedDate = DateTime.Now;
                logDal.Save(log);
            }
            return Newtonsoft.Json.JsonConvert.SerializeObject(helper);
        }
        private string SetNonActive(HttpContext context, JObject jo)
        {
            JSONHelper helper = new JSONHelper();

            try
            {
                var strId = (string)jo["SectorRegistrationID"];

                string[] ids = strId.Split(',');
                foreach (string id in ids)
                {
                    int intID = Convert.ToInt32(id);
                    recRegistrationDal.SetNonActive(intID);
                }

                helper.IsSuccess = true;
                helper.Message = "Data berhasil di hapus !";
            }
            catch (Exception ex)
            {
                helper.IsSuccess = false;
                helper.Message = ex.Message;
            }
            finally
            {
                recLog log = new recLog();
                log.LogEvent = "Delete";
                log.LogMessage = helper.Message;
                log.LogSource = "srecRegistrationhandler.ashx";
                log.CreatedBy = base.UserLoggedIn;
                log.CreatedDate = DateTime.Now;
                logDal.Save(log);
            }
            return Newtonsoft.Json.JsonConvert.SerializeObject(helper);
        }

        private string GetData(HttpContext context)
        {
            var userTypes = ConfigurationManager.AppSettings["UserType"].Trim().Split(','); // Based On Web.Config
            var internalUserID = int.Parse(userTypes[2].Trim().Split(':')[0]); // Get UserTypeID of Internal User

            //var subsubSectorIds = "";
            //subsubSectorIds = " WHERE " + "trsr.SubSubSectorID IN (" + base.SubSubSectorIDs + ")";
            //var registrationId = "AND trr.RegistrationID = " + base.RegistrationID;
            //var userTypeId = base.UserTypeID;
            //string parameters = Helper.JqGridFilterToSQLParams(context);
            //if (parameters == "")
            //{
            //    if (userTypeId == null || userTypeId == 3)
            //        parameters = parameters + subsubSectorIds;
            //    else
            //        parameters = parameters + subsubSectorIds + registrationId;
            //}
            //else
            //{
            //    subsubSectorIds = " AND " + "trsr.SubSubSectorID IN (" + base.SubSubSectorIDs + ")";
            //    if (userTypeId == null || userTypeId == 3)
            //        parameters = parameters + subsubSectorIds;
            //    else
            //        parameters = parameters + subsubSectorIds + registrationId;
            //}
            string subsubSectorIds = string.Empty;
            int registrationId = 0;
            recUserSectorRepo userSectorDal = new recUserSectorRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);
            List<recUserSector> sectors = userSectorDal.GetListByUserId(base.UserID);
            sectors.ForEach(delegate(recUserSector obj)
            {
                subsubSectorIds += obj.SubSubSectorID.ToString() + ",";
            });
            if (!string.IsNullOrEmpty(subsubSectorIds))
                subsubSectorIds = subsubSectorIds.Remove(subsubSectorIds.Length - 1);

            recRegistrationRepo registrationDal = new recRegistrationRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);
            recRegistration registration = registrationDal.GetByUserId(base.UserID);
            if (registration != null)
                registrationId = registration.RegistrationID;
            string parameters = Helper.JqGridFilterToSQLParams(context);

            if (!string.IsNullOrEmpty(parameters)) // If Current User while using filter on Grid
            {
                if (!string.IsNullOrEmpty(subsubSectorIds))
                {
                    parameters += " AND SubSubSectorID IN (" + subsubSectorIds + ")";
                }

                if (!base.UserTypeID.Value.Equals(internalUserID)) // User is not an Internal User -> (Calon Debitur / Debitur)
                {
                    //if (base.RegistrationID > 0)
                    //{
                    parameters += " AND RegistrationID = " + registrationId;
                    //}
                }
            }
            else
            {
                if (!string.IsNullOrEmpty(subsubSectorIds))
                {
                    parameters = " WHERE SubSubSectorID IN (" + subsubSectorIds + ")";
                }

                if (!base.UserTypeID.Value.Equals(internalUserID)) // User is not an Internal User -> (Calon Debitur / Debitur)
                {
                    //if (base.RegistrationID > 0)
                    //{
                    parameters += " AND RegistrationID = " + registrationId;
                    //}
                }
            }

            int page = 1;
            int.TryParse(context.Request.QueryString["page"] ?? "1", out page);

            int pageSize = 10;
            int.TryParse(context.Request.QueryString["rows"] ?? "10", out pageSize);

            string sidx = ((context.Request.QueryString["sidx"]) == null ? recRegistration.Fields.RegistrationID : context.Request.QueryString["sidx"].ToString());

            string sord = ((context.Request.QueryString["sord"]) == null ? "ASC" : context.Request.QueryString["sord"].ToString());

            int totalCount = 0;
            int totalPages = 0;
            List<recRegistration> pagedList = null;

            try
            {
                totalCount = Convert.ToInt32(recRegistrationDal.SelectScalar(SQL.Function.Aggregate.Count, "SectorRegistrationID", parameters));

                decimal dc = decimal.Ceiling(Convert.ToDecimal(totalCount) / Convert.ToDecimal(pageSize));
                totalPages = Convert.ToInt32(dc);

                pagedList = recRegistrationDal.GetList(pageSize, page, sidx, sord, parameters);
            }
            catch (Exception ex)
            {
                recLog log = new recLog();
                log.LogEvent = "GetData";
                log.LogMessage = ex.Message;
                log.LogSource = "recRegistrationhandler.ashx";
                log.CreatedBy = base.UserLoggedIn;
                log.CreatedDate = DateTime.Now;
                logDal.Save(log);
                log.Dispose();
            }
            finally
            {

            }

            // send params into jqGrid's format
            var gridData = new
            {
                page = page,
                total = totalPages,
                records = totalCount,
                rows = pagedList,
            };

            return Newtonsoft.Json.JsonConvert.SerializeObject(gridData);

        }


    }

}